package cn.xwj.poiExcle;

import com.sun.prism.shader.Solid_TextureYV12_AlphaTest_Loader;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.swing.text.Style;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

/**
 * Created by thinkjoy on 2017/6/19.
 * 写入数据到Excel
 */
public class PoIExcelInPut {

    /**
     * 设置字体大小和居中
     * @param wb
     * @param fontsize
     * @param i
     */
    public void setMyStyle(HSSFWorkbook wb, short fontsize, int i, int index){
        Font font=wb.createFont();
        CellStyle style=wb.createCellStyle();
        font.setFontHeightInPoints(fontsize);//设置字体大小
        //设置居中显示
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        wb.getSheet("第一页").getRow(i).getCell(index).setCellStyle(style);
    }

    public void createExcel(){
        HSSFWorkbook wb=new HSSFWorkbook();//生成Workbook
        HSSFSheet sheet=wb.createSheet("第一页");//创建HSSFSheet对象
        HSSFRow row=sheet.createRow(0);//创建行对象,参数为行索引(excel的行)
        row.setHeight((short) 1000);//设置行高
        HSSFCell cell=row.createCell(0);//创建单元格对象,参数为列索引
        cell.setCellValue("学员成绩一览表");//第一行设置表名
        setMyStyle(wb,(short) 30,0,0);

        //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));

        HSSFRow row1=sheet.createRow(1);//创建第二行
        row1.setHeight((short) 500);
        row1.createCell(0).setCellValue("姓名");
        row1.createCell(1).setCellValue("班级");
        row1.createCell(2).setCellValue("笔试成绩");
        row1.createCell(3).setCellValue("机试成绩");
        setMyStyle(wb,(short) 15,1,0);
        setMyStyle(wb,(short) 15,1,1);
        setMyStyle(wb,(short) 15,1,2);
        setMyStyle(wb,(short) 15,1,3);

        /**
         * 下面是具体内容,表格具体内容
         */
        for(int i=0;i<10;i++){
            HSSFRow rowI=sheet.createRow(i+2);
            rowI.setHeight((short) 500);
            rowI.createCell(0).setCellValue("许伟杰");
            rowI.createCell(1).setCellValue("2班");
            rowI.createCell(2).setCellValue("85");
            rowI.createCell(3).setCellValue("100");
            setMyStyle(wb,(short) 10,i+2,0);
            setMyStyle(wb,(short) 10,i+2,1);
            setMyStyle(wb,(short) 10,i+2,2);
            setMyStyle(wb,(short) 10,i+2,3);
        }

//        导出Excel文件
        try {
            FileOutputStream output=new FileOutputStream("F:\\workbook.xls");
            wb.write(output);
            output.flush();
            output.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}